package com.sxp.dao;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import com.sxp.model.FoodSelection;
import com.sxp.model.MonthlyCount;
import com.sxp.util.DBUtil;

public class FoodSelectionDaoImpl implements FoodSelectionDao{
    @Override
    public void setFoodSelections(List<FoodSelection> foodSelections) {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            PreparedStatement pStmt1 = conn.prepareStatement("UPDATE food_selection SET rice_id = ?, meal_id = ? WHERE username = ? AND date = ?");
            PreparedStatement pStmt2 = conn.prepareStatement("INSERT INTO food_selection(username,date,rice_id,meal_id) VALUES(?,?,?,?)");
            for(FoodSelection foodSelection : foodSelections) {
                pStmt1.setInt(1, foodSelection.getRiceId());
                pStmt1.setInt(2, foodSelection.getMealId());
                pStmt1.setString(3, foodSelection.getUserName());
                pStmt1.setDate(4, new java.sql.Date(foodSelection.getYear()-1900,foodSelection.getMonth(),foodSelection.getDay()));

                pStmt2.setString(1, foodSelection.getUserName());
                pStmt2.setDate(2, new java.sql.Date(foodSelection.getYear()-1900,foodSelection.getMonth(),foodSelection.getDay()));
                pStmt2.setInt(3, foodSelection.getRiceId());
                pStmt2.setInt(4, foodSelection.getMealId());

                if(pStmt1.executeUpdate()==0) {
                    pStmt2.executeUpdate();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
    }

    @Override
    public List<FoodSelection> getFoodSelections(String username, int month) {
        List<FoodSelection>foodSelections = new ArrayList<FoodSelection>();

        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            PreparedStatement pStmt = null;
            if(month == 1){
                pStmt = conn.prepareStatement("SELECT username,DAY(date) AS dd,MONTH(date) AS mm,YEAR(date) AS yyyy,rice_id,meal_id FROM food_selection WHERE username=? AND (date BETWEEN (DATE_SUB(CURDATE(),INTERVAL (DAY(CURDATE())-1) DAY)) AND (DATE_SUB(DATE_ADD(CURDATE(),INTERVAL 1 MONTH),INTERVAL (DAY(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))) DAY))) ORDER BY dd ASC");
            }else if(month == 2){
                pStmt = conn.prepareStatement("SELECT username,DAY(date) AS dd,MONTH(date) AS mm,YEAR(date) AS yyyy,rice_id,meal_id FROM food_selection WHERE username=? AND (date BETWEEN (DATE_ADD(DATE_SUB(CURDATE(),INTERVAL (DAY(CURDATE())-1) DAY),INTERVAL 1 MONTH)) AND (DATE_SUB(DATE_ADD(CURDATE(),INTERVAL 2 MONTH),INTERVAL (DAY(DATE_ADD(CURDATE(),INTERVAL 2 MONTH))) DAY))) ORDER BY dd ASC");
            }
            else if(month == 3){
                pStmt = conn.prepareStatement("SELECT username,DAY(date) AS dd,MONTH(date) AS mm,YEAR(date) AS yyyy,rice_id,meal_id FROM food_selection WHERE username=? AND (date BETWEEN (DATE_ADD(DATE_SUB(CURDATE(),INTERVAL (DAY(CURDATE())-1) DAY),INTERVAL 2 MONTH)) AND (DATE_SUB(DATE_ADD(CURDATE(),INTERVAL 3 MONTH),INTERVAL (DAY(DATE_ADD(CURDATE(),INTERVAL 3 MONTH))) DAY))) ORDER BY dd ASC");
            }

            pStmt.setString(1, username);
            ResultSet resultSet = pStmt.executeQuery();

            while (resultSet.next()){
                FoodSelection foodSelection = new FoodSelection();

                foodSelection.setUserName(resultSet.getString("username"));
                foodSelection.setDay(resultSet.getInt("dd"));
                foodSelection.setMonth(resultSet.getInt("mm"));
                foodSelection.setYear(resultSet.getInt("yyyy"));
                foodSelection.setRiceId(resultSet.getInt("rice_id"));
                foodSelection.setMealId(resultSet.getInt("meal_id"));

                foodSelections.add(foodSelection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
        return foodSelections;
    }

    @Override
    public void addFoodSelection(FoodSelection foodSelection) {
        Connection conn=null;
        try {

            conn = DBUtil.getConnection();
            PreparedStatement pStmt = conn
                    .prepareStatement("insert into food_selection(username,date,rice_id,meal_id) values (?, ?, ?, ? )");

            pStmt.setString(1, foodSelection.getUserName());
            pStmt.setDate(2, new java.sql.Date(foodSelection.getDate().getTime()));
            pStmt.setInt(3,foodSelection.getRiceId());
            pStmt.setInt(4,foodSelection.getMealId());
            pStmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
    }

    @Override
    public void deleteFoodSelection() {
    }



    public List<FoodSelection> getFoodSelection() {
        Connection conn = null;
        List<FoodSelection> fsData = new ArrayList<FoodSelection>();
        try {
            conn = DBUtil.getConnection();
            String sql="select mx.username, mx.date ,mx.rice_id, mx.meal_id from (select u.username,fd.date,fd.rice_id,fd.meal_id from user u left outer join food_selection fd on fd.username = u.username where date=CURDATE() union all select u.username, null, null, null from user u where u.username not in(select username from food_selection where date=CURDATE() order by username asc)order by username asc ) mx ;";
            PreparedStatement prepStmt = conn.prepareStatement(sql);
            ResultSet rSet = prepStmt.executeQuery( );

            while (rSet.next()) {
                FoodSelection fs = new FoodSelection();
                fs.setUserName(rSet.getString("username"));
                fs.setDate(rSet.getDate("date"));
                fs.setRiceId(rSet.getInt("rice_id"));
                fs.setMealId(rSet.getInt("meal_id"));
                fsData.add(fs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }

        return fsData;
    }




    public List<FoodSelection> getFoodForDate(String date) throws ParseException, SQLException {
        String selectedDate=date;

        SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date dateS=sd.parse(selectedDate);
        java.sql.Date sqlD = new java.sql.Date(dateS.getTime());
        System.out.println(dateS);

        Connection conn = null;

        List<FoodSelection> fsDataDate = new ArrayList<FoodSelection>();

        try {
            conn = DBUtil.getConnection();

            String sql="select * from food_selection where date=?";
            PreparedStatement prepStmt = conn.prepareStatement(sql);
            prepStmt.setDate(1,sqlD);
            ResultSet rSet = prepStmt.executeQuery( );

            while (rSet.next()) {
                FoodSelection fs = new FoodSelection();
                fs.setUserName(rSet.getString("username"));
                fs.setDate(rSet.getDate("date"));
                fs.setRiceId(rSet.getInt("rice_id"));
                fs.setMealId(rSet.getInt("meal_id"));
                fsDataDate.add(fs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
        return fsDataDate;
    }


    public List<FoodSelection> getAllFoodSForDate(String date) throws ParseException, SQLException {
        String selectedDate=date;
        SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date dateS=sd.parse(selectedDate);
        java.sql.Date sqlD = new java.sql.Date(dateS.getTime());
//        System.out.println(dateS);
        Connection conn = null;
        List<FoodSelection> fsDataDate = new ArrayList<FoodSelection>();
        try {
            conn = DBUtil.getConnection();

            String sql="select mx.username, mx.date ,mx.rice_id, mx.meal_id from (select u.username,fd.date,fd.rice_id,fd.meal_id from user u left outer join food_selection fd on fd.username = u.username where date=? union all select u.username, null, null, null from user u where u.username not in(select username from food_selection where date=? order by username asc) order by username asc) mx ;";
            PreparedStatement prepStmt = conn.prepareStatement(sql);
            prepStmt.setDate(1,sqlD);
            prepStmt.setDate(2,sqlD);
            ResultSet rSet = prepStmt.executeQuery( );

            while (rSet.next()) {
                FoodSelection fs = new FoodSelection();
                fs.setUserName(rSet.getString("username"));
                fs.setDate(rSet.getDate("date"));
                fs.setRiceId(rSet.getInt("rice_id"));
                fs.setMealId(rSet.getInt("meal_id"));
                fsDataDate.add(fs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
        return fsDataDate;
    }



    public FoodSelection getFoodSelectionByName(String editingUser, String date) throws ParseException {
        String editUser=editingUser;
        String editDate=date;
        SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM-dd");

        java.util.Date editD=sd.parse(editDate);
        java.sql.Date sqlD=new java.sql.Date(editD.getTime());

        Connection conn=null;
        FoodSelection fdSelItem=new FoodSelection();
        try{
            conn=DBUtil.getConnection();
            String sql2=("select mx.username, mx.date ,mx.rice_id, mx.meal_id from ( select u.username,fd.date,fd.rice_id,fd.meal_id from user u left outer join food_selection fd on fd.username = u.username where date=? and name=? union all select u.username, null, null, null from user u where u.username not in(select username from food_selection where date=? and name=?)) mx ;");
            PreparedStatement prepStmt2 = conn.prepareStatement(sql2);
            prepStmt2.setString(1, editUser);
            prepStmt2.setDate(2,sqlD);
            prepStmt2.setString(3,editUser);
            prepStmt2.setDate(4,sqlD);
            ResultSet rSet2 = prepStmt2.executeQuery( );

            while (rSet2.next()) {

                fdSelItem.setUserName(rSet2.getString("username"));
                fdSelItem.setDate(rSet2.getDate("date"));
                fdSelItem.setRiceId(rSet2.getInt("rice_id"));
                fdSelItem.setMealId(rSet2.getInt("meal_id"));

            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.closeConnection(conn);
        }
        return fdSelItem;
    }

    public FoodSelection getFoodSOnlByName(String user) {
        String username=user;
        Connection conn=null;

        FoodSelection fdS=new FoodSelection();
        try{
            String sqlQuery="select u.username,fd.date,fd.rice_id,fd.meal_id from user u left join food_selection fd on u.username=fd.username where u.username=? ;";
            PreparedStatement prep= conn.prepareStatement(sqlQuery);
            prep.setString(1, username);
            ResultSet rS= prep.executeQuery();
            while (rS.next()) {

                fdS.setUserName(rS.getString("username"));
                fdS.setDate(rS.getDate("date"));
                fdS.setRiceId(rS.getInt("rice_id"));
                fdS.setMealId(rS.getInt("meal_id"));
            }

        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.closeConnection(conn);
        }
        return fdS;
    }

    public void updateSelection(FoodSelection foodS) {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            PreparedStatement pStmt1 = conn.prepareStatement("update food_selection set rice_id=?, meal_id=? where username=? and  date=?");
            PreparedStatement pStmt2 = conn.prepareStatement("insert into food_selection(username,date,rice_id,meal_id) VALUES(?,?,?,?)");

//            pStmt.setString(1, foodS.getUsername());

            pStmt1.setInt(1, foodS.getRiceId());
            pStmt1.setInt(2, foodS.getMealId());
            pStmt1.setString(3, foodS.getUserName());
            pStmt1.setDate(4, new java.sql.Date(foodS.getDate().getTime()));

            pStmt2.setString(1, foodS.getUserName());
            pStmt2.setDate(2,  new java.sql.Date(foodS.getDate().getTime()));
            pStmt2.setInt(3, foodS.getRiceId());
            pStmt2.setInt(4, foodS.getMealId());

            if(pStmt1.executeUpdate()==0) {
                pStmt2.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
    }
    @Override
    public List<MonthlyCount> getCurrentMonthMealCount() {
        Connection conn = null;
        List<MonthlyCount> mealCount=new ArrayList();
        try {
            conn = DBUtil.getConnection();

            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select meal_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by meal_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select meal_type.id,meal_type.type,extract(day from date),count(*) from food_selection left join meal_type on food_selection.meal_id=meal_type.id where extract(month from date)=extract(month from NOW()) and food_selection.rice_id<>0 group by meal_id,date order by date");

            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();
                mc.setMeal_id(rSet.getInt("meal_type.id"));
                mc.setMealName(rSet.getString("meal_type.type"));
                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                mealCount.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
        return mealCount;
    }

    @Override
    public List<MonthlyCount> getPreviousMonthMealCount() {
        Connection conn = null;
        List<MonthlyCount> mealCount=new ArrayList();
        try {
            conn = DBUtil.getConnection();

            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select meal_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by meal_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select meal_type.id,meal_type.type,extract(day from date),count(*) from food_selection left join meal_type on food_selection.meal_id=meal_type.id where extract(month from date)=extract(month from NOW() - INTERVAL 1 MONTH) and food_selection.rice_id<>0 group by meal_id,date order by date");

            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();
                mc.setMeal_id(rSet.getInt("meal_type.id"));
                mc.setMealName(rSet.getString("meal_type.type"));
                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                mealCount.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
        return mealCount;
    }

    @Override
    public List<MonthlyCount> getNextMonthMealCount() {
        Connection conn = null;
        List<MonthlyCount> mealCount=new ArrayList();
        try {
            conn = DBUtil.getConnection();

            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select meal_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by meal_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select meal_type.id,meal_type.type,extract(day from date),count(*) from food_selection left join meal_type on food_selection.meal_id=meal_type.id where extract(month from date)=extract(month from NOW() + INTERVAL 1 MONTH) and food_selection.rice_id<>0 group by meal_id,date order by date");

            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();
                mc.setMeal_id(rSet.getInt("meal_type.id"));
                mc.setMealName(rSet.getString("meal_type.type"));
                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                mealCount.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }
        return mealCount;
    }



    @Override
    public List<MonthlyCount> getCurrentMonthRiceCount() {
        Connection conn = null;
        List<MonthlyCount> ricecount = new ArrayList();
        try {
            conn = DBUtil.getConnection();
            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select rice_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by rice_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select rice_type.id,rice_type.type,extract(day from date),count(*) from food_selection left join rice_type on food_selection.rice_id=rice_type.id where extract(month from date)=extract(month from NOW()) group by rice_id,date order by date");
            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();
                mc.setMeal_id(rSet.getInt("rice_type.id"));
                mc.setRiceName(rSet.getString("rice_type.type"));
                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                ricecount.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }

        return ricecount;
    }

    @Override
    public List<MonthlyCount> getPreviousMonthRiceCount() {
        Connection conn = null;
        List<MonthlyCount> ricecount = new ArrayList();
        try {
            conn = DBUtil.getConnection();
            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select rice_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by rice_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select rice_type.id,rice_type.type,extract(day from date),count(*) from food_selection left join rice_type on food_selection.rice_id=rice_type.id where extract(month from date)=extract(month from NOW() - INTERVAL 1 MONTH) group by rice_id,date order by date");
            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();
                mc.setMeal_id(rSet.getInt("rice_type.id"));
                mc.setRiceName(rSet.getString("rice_type.type"));
                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                ricecount.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }

        return ricecount;
    }

    @Override
    public List<MonthlyCount> getNextMonthRiceCount() {
        Connection conn = null;
        List<MonthlyCount> ricecount = new ArrayList();
        try {
            conn = DBUtil.getConnection();
            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select rice_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by rice_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select rice_type.id,rice_type.type,extract(day from date),count(*) from food_selection left join rice_type on food_selection.rice_id=rice_type.id where extract(month from date)=extract(month from NOW() + INTERVAL 1 MONTH) group by rice_id,date order by date");
            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();
                mc.setMeal_id(rSet.getInt("rice_type.id"));
                mc.setRiceName(rSet.getString("rice_type.type"));
                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                ricecount.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }

        return ricecount;
    }

    @Override
    public List<MonthlyCount> getCurrentMonthTotal() {
        Connection conn = null;
        List<MonthlyCount> currentMonthTotalByDate = new ArrayList();
        try {
            conn = DBUtil.getConnection();
            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select rice_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by rice_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from NOW()) and rice_id<>0 group by date");
            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();

                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                currentMonthTotalByDate.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }

        return currentMonthTotalByDate;
    }

    @Override
    public List<MonthlyCount> getPreviousMonthTotal() {
        Connection conn = null;
        List<MonthlyCount> previousMonthTotalByDate = new ArrayList();
        try {
            conn = DBUtil.getConnection();
            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select rice_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by rice_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from NOW() - INTERVAL 1 MONTH) and rice_id<>0 group by date");
            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();

                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                previousMonthTotalByDate.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }

        return previousMonthTotalByDate;
    }

    @Override
    public List<MonthlyCount> getNextMonthTotal() {
        Connection conn = null;
        List<MonthlyCount> nextMonthTotalByDate = new ArrayList();
        try {
            conn = DBUtil.getConnection();
            Statement stmt = conn.createStatement();
            //ResultSet rSet = stmt.executeQuery("select rice_id,extract(month from date),extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from curdate()) group by rice_id,date order by date");
            ResultSet rSet = stmt.executeQuery("select extract(day from date),count(*) from food_selection where extract(month from date)=extract(month from NOW() + INTERVAL 1 MONTH) and rice_id<>0 group by date");
            while (rSet.next()) {

                MonthlyCount mc=new MonthlyCount();

                mc.setDay(rSet.getInt("extract(day from date)"));
                mc.setCount(rSet.getInt("count(*)"));
                nextMonthTotalByDate.add(mc);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn);
        }

        return nextMonthTotalByDate;
    }


}